using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace LiveStock.dataAccess
{
    /// <summary>
    /// Data Access Layer class for Companies table
    /// </summary>
    public class CompaniesData
    {
        private CompaniesData() { }

        public static Companies.CompaniesDataTable CompaniesGetByUserID(int userID)
        {
            Companies.CompaniesDataTable cdt = new Companies.CompaniesDataTable();
            using (SqlCommand cmd = DBManager.GetCommand("CompaniesGetByUserID"))
            {
                cmd.Parameters.AddWithValue("UserID", userID);
                DBManager.FillDataTable(cdt, cmd);
            }
            return cdt;
        }

        public static Boolean CompanyAlreadySaved(string symbol, int userID)
        {
            Companies.CompaniesDataTable cdt = new Companies.CompaniesDataTable();

            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Companies WHERE Symbol = '" + symbol + "' AND UserID = '" + userID + "'"))
            {
                SqlConnection conn = DBManager.GetConnection();
                cmd.Connection = conn;
                DBManager.FillDataTable(cdt, cmd);
            }
            if (cdt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static int CompanyInsert(Companies.CompaniesRow cr)
        {
            int id = 0;
            using (SqlCommand cmd = DBManager.GetCommand("CompanyInsert"))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("UserID", cr.UserID);
                cmd.Parameters.AddWithValue("CompanyName", cr.CompanyName);
                cmd.Parameters.AddWithValue("Symbol", cr.Symbol);
                cmd.Parameters.AddWithValue("Exchange", cr.Exchange);
                id = Convert.ToInt32(DBManager.ExecuteScalar(cmd));
            }
            return id;
        }

        public static Companies.CompaniesDataTable CompanyGetByID(int companyID)
        {
            Companies.CompaniesDataTable cdt = new Companies.CompaniesDataTable();
            using (SqlCommand cmd = DBManager.GetCommand("CompanyGetByID"))
            {
                cmd.Parameters.AddWithValue("CompanyID", companyID);
                DBManager.FillDataTable(cdt, cmd);
            }
            return cdt;
        }

        public static int CompanyDeleteByID(int companyID, int userID)
        {
            int ar = 0;
            using (SqlCommand cmd = DBManager.GetCommand("CompanyDeleteByID"))
            {
                cmd.Parameters.AddWithValue("CompanyID", companyID);
                cmd.Parameters.AddWithValue("UserID", userID);
                ar = DBManager.ExecuteNonQuery(cmd);
            }
            return ar;
        }

        //TODO: Needs stored procedure CompanyUpdateByID on db.
        //public static int CompanyUpdateByID(Companies.CompaniesRow cr)
        //{
        //    int ar = 0;
        //    using (SqlCommand cmd = DBManager.GetCommand("CompanyUpdateByID"))
        //    {
        //        cmd.Parameters.AddWithValue("CompanyID", cr.CompanyID);
        //        cmd.Parameters.AddWithValue("CompanyName", cr.CompanyName);
        //        cmd.Parameters.AddWithValue("Symbol", cr.Symbol);
        //        cmd.Parameters.AddWithValue("Exchange", cr.Exchange);
        //        ar = DBManager.ExecuteNonQuery(cmd);
        //    }
        //    return ar;
        //}
    }
}
